#!/opt/lampp/bin/perl -w
use strict;
use warnings;
use DBI;

require('function.pl');

my @cities = load_every_cities();
my $db_host = 'localhost';
my $db = 'excel';
my $db_user = 'zjxia';
my $db_password = 'bblovebb';
my $db_conn = DBI->connect("DBI:mysql:database=$db;host=$db_host", $db_user, $db_password);
# $db_conn->do('set character_set_client =gb2312; set character_set_connection =gb2312; set character_set_database =gb2312; set character_set_results =gb2312; set character_set_server =gb2312; set character_set_system =gb2312; SET collation_server = gb2312_chinese_ci; SET collation_database = gb2312_chinese_ci; SET collation_connection =gb2312_chinese_ci');
$db_conn->do('set names utf8');

my $sql = "select addr from excel where city = ''";
my $request = $db_conn->prepare($sql);
my $result = $request->execute();
while (my ($addr) = $request->fetchrow_array) {
	my $city = '未知';
	foreach my $candidate (@cities) {
		if (index($addr, $candidate) >= 0) {
			$city = $candidate;
			last;
		}
	}
	$sql = "update excel set city = '$city' where addr = '$addr'";
	$db_conn->do($sql);
	print "$city\t$addr\n";
}

